package com.cpi.cornpp.region.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.region.dto.RegionDTO;
import com.cpi.cornpp.region.exception.RegionException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class RegionDAO extends DBDAO {

	private static final String SQL_GET_REGION= "select region_id, short_name, description, app_default from regions";//$NON-NLS-1$
	
	public List list()throws RegionException{
		List regions = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		RegionDTO regionDTO = null;
		try{
			regions = new ArrayList(0);
			pstm = prepareSQL(SQL_GET_REGION, dsName);
			rs = executeQuery(pstm);
			while (rs.next()){
				regionDTO = new RegionDTO();
				regionDTO.setRegionId(rs.getInt(1));
				regionDTO.setShortName(rs.getString(2));
				regionDTO.setDescription(rs.getString(3));
				regionDTO.setAppDefault(rs.getByte(4));
				regions.add(regionDTO);
			}
		}catch (PoolException poolException){
			RegionException regionException = null;
			poolException.printStackTrace(System.out);
			regionException = new RegionException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw regionException;
		} catch(SQLException sqlException){
			RegionException regionException = null;
			sqlException.printStackTrace(System.out);
			regionException = new RegionException (sqlException, CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw regionException;
		}finally {
			try{
				closeConnection(pstm.getConnection());
			}catch(Exception exception){
			}
		}
		return regions;
	}
}